package com.common.android.utils;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

/**
 * 针对数据库升级需要使用，比如修改表结构，需要增加表字段。
 * <br>Created 2014-4-24下午7:19:52
 * @version  Ver 1.1
 * @author   suchangjun		
 * @see 	 
 * Copyright(c) 2009-2014, TQ Digital Entertainment, All Rights Reserved
 *
 */
public class AlertDbUtils {

    /**
     * 获取数据库一个表中所有列
     * <br>Created 2014-4-22下午5:51:28
     * @param db 数据库
     * @param tableName 数据库的表名
     * @return 数据库一个表中所有列字符串
     * @author       suchangjun
     */
	public static String getColumnNames(SQLiteDatabase db, String tableName) {
		StringBuffer sb = null;
		Cursor c = null;
		try {
			c = db.rawQuery("PRAGMA table_info(" + tableName + ")", null);
			if (null != c) {
				int columnIndex = c.getColumnIndex("name");
				if (-1 == columnIndex) {
					return null;
				}
	
				sb = new StringBuffer(c.getCount());
				for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
					sb.append(c.getString(columnIndex));
					sb.append(",");
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			Log.e("suchangjun ", "升级数据库失败 " + tableName + "查询列失败");
		} finally {
			if (c != null) {
				c.close();
			}
		}
		if (sb != null) {
			return sb.toString();
		}
		return null;
	}

	/**
	 * 修改表结构，增加字段。
	 * <br>Created 2014-4-24下午7:18:33
	 * @param db 数据库
	 * @param tableName 数据库的表名
	 * @param columns 列名 
	 * @param createNewTableSql 创建新表的的SQL语句
	 * @author       suchangjun
	 */
	public static void alertAddColumnTable(SQLiteDatabase db, String tableName,
			String columns, String createNewTableSql) {
	
		try {
	
			db.beginTransaction();
			String reColumn = columns.substring(0, columns.length() - 1);
			// rename the table
			String tempTable = tableName + "texp_temptable";
			String sql = "alter table " + tableName + " rename to " + tempTable;
			db.execSQL(sql);
	
			// drop the oldtable
			String dropString = "drop table if exists " + tableName;
			db.execSQL(dropString);
			// creat table
			db.execSQL(createNewTableSql);
	
			// load data
			String ins = "insert into " + tableName + " (" + reColumn + ") "
					+ "select " + reColumn + "" + " " + " from " + tempTable;
	
			db.execSQL(ins);
	
			// drop the tempTable
			dropString = "drop table if exists " + tempTable;
			db.execSQL(dropString);
	
			db.setTransactionSuccessful();
		} catch (Exception e) {
			Log.e("suchangjun ", "升级数据库失败 " + tableName + e.getMessage());
		} finally {
			db.endTransaction();
		}
	
	}

}
